<?php
require_once('./cherylib.php');
require_once('../src/scottlib.php');

// single parameter, no escaping
assert_equal('SELECT @@VERSION', prepare('SELECT %p', '@@VERSION'));

// single parameter, escaping
assert_equal("SELECT * FROM `table` WHERE column='value'", prepare('SELECT * FROM `table` WHERE column=?', 'value'));

// both
assert_equal("SELECT * FROM `table` WHERE column='value'", prepare('SELECT * FROM # WHERE column=?', array('table', 'value')));

// single parameter, no escaping because numeric
assert_equal("SELECT 3.1415 4 0.0003 'value'", prepare('SELECT ? ? ? ?', array(3.1415, 4, 0.0003, 'value')));

// with new lines and tabs
assert_equal("
	SELECT *
	FROM `table`
	WHERE
		column = 'value'
", prepare("
	SELECT *
	FROM #
	WHERE
		column = ?
", array('table', 'value')));

// named parameters
assert_equal(
	"SELECT * FROM `table` WHERE `column`='value1' OR `column`='value2'", 
	prepare("SELECT * FROM `table` WHERE `column`=:param1 OR `column`=:param2", array(
		'param1' => 'value1', 
		'param2' => 'value2'
	))
);

// named parameters with optional (null) replacement
assert_equal(
	"SELECT * FROM `table` WHERE `column`=null OR `column`='value2'", 
	prepare("SELECT * FROM `table` WHERE `column`=!:param1 OR `column`=:param2", array(
		'param2' => 'value2'
	))
); 

// named parameters and numeric parameter values
assert_equal(
	"SELECT * FROM `table` WHERE `column1`=3.1415 AND `column2`=4 AND `column3`=0.0003", 
	prepare("SELECT * FROM `table` WHERE `column1`=:param1 AND `column2`=:param2 AND `column3`=:param3", array(
		'param1' => 3.1415, 
		'param2' => 4, 
		'param3' => 0.0003
	))
);

// named parameters for system objects
assert_equal(
	"SELECT * FROM `foo` WHERE `bar`=1 OR `bar`='value'", 
	prepare("SELECT * FROM #table WHERE #column=:param1 OR #column=:param2", array(
		'table' => 'foo', 
		'column' => 'bar', 
		'param1' => 1, 
		'param2' => 'value'
	))
);

// calendar
assert_equal(
	"SELECT * FROM table WHERE date='1981/10/26 05:00:00'",
	prepare("SELECT * FROM table WHERE date=%c", '10/26/1981 5am')
); 

// numeric
assert_equal(
	"SELECT * FROM table WHERE number=3.1415 OR number=5",
	prepare("SELECT * FROM table WHERE number=%d OR number=%n", array(
		3.1415,
		5
	))
);

// string
assert_equal(
	"SELECT * FROM table WHERE value='foo'",
	prepare("SELECT * FROM table WHERE value=%s", 'foo')
);


